--Temp tables
DECLARE @TempUsers TABLE (ID INT PRIMARY KEY NOT NULL IDENTITY(1,1),UserName VARCHAR(25));
DECLARE @TempGroups TABLE (ID INT PRIMARY KEY NOT NULL IDENTITY(1,1), GroupName VARCHAR(25),UserID INT);
DECLARE @TempItems TABLE (ID INT PRIMARY KEY NOT NULL IDENTITY(1,1),ItemName VARCHAR(25),GroupID INT, UserID INT);

--Temp Tables Population
INSERT INTO @TempUsers SELECT 'Joe' UNION SELECT 'Fred';
INSERT INTO @TempGroups SELECT 'TestGr1',1 UNION SELECT 'TestGr2',1;
INSERT INTO @TempItems SELECT 'Item1',1,1 UNION SELECT 'Item2',1,1;
INSERT INTO @TempItems SELECT 'Item3',2,1 UNION SELECT 'Item4',2,1;

--Look at the initial data
SELECT 'OldUsers' AS TableName,* FROM @TempUsers
SELECT 'OldGroups' AS TableName,* FROM @TempGroups
SELECT 'OldItems' AS TableName,* FROM @TempItems

--Declare some helper variables
DECLARE @TempCorrelationTable TABLE (OldID INT, [NewID] INT);
DECLARE @OldUserID INT = 1;
DECLARE @NewUserID INT = 2;

--Merge the groups to the new user
MERGE INTO @TempGroups AS T
USING (SELECT ID,GroupName FROM @TempGroups) AS S
ON 1=2
WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (GroupName,@NewUserID)
OUTPUT S.ID, INSERTED.ID INTO @TempCorrelationTable;

--Merge the items to the new user					
MERGE INTO @TempItems AS T
USING (SELECT ItemName,[NewID] FROM @TempItems AS TI INNER JOIN @TempCorrelationTable AS TC ON (TI.GroupID = TC.OldID)) AS S
ON 1=2
WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (ItemName,[NewID],@NewUserID);

--Look at the merged data
SELECT 'NewUsers' AS TableName,* FROM @TempUsers
SELECT 'NewGroups' AS TableName,* FROM @TempGroups
SELECT 'NewItems' AS TableName,* FROM @TempItems
